package com.xinyirun.scm.core.system.mapper.query.contract;

import cn.hutool.core.lang.Dict;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xinyirun.scm.bean.system.vo.report.contract.PurchaseContractStatisticsExportVo;
import com.xinyirun.scm.bean.system.vo.report.contract.PurchaseContractStatisticsVo;
import com.xinyirun.scm.bean.system.vo.report.contract.SalesContractStatisticsVo;
import com.xinyirun.scm.common.constant.DictConstant;
import com.xinyirun.scm.common.constant.SystemConstants;
import com.xinyirun.scm.core.system.config.mybatis.typehandlers.JsonArrayTypeHandler;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ContractStatisticsMapper {

    @Select(""
            +  "  SELECT                                                                                                "
            +  "  	t1.contract_no,                                                                                     "
            +  "  	t3.`warehouse_name`,                                                                                "
            +  "  	t5.`name` goods_name,                                                                               "
            +  "  	t4.`spec` ,                                                                                         "
            +  "  	SUM( t.num ) contract_num,                                                                          "
            +  "  	t6.actual_count in_qty,                                                                             "
            +  "  	tab4.actual_count today_out_qty,                                                                    "
            +  "  	tab5.actual_count out_qty,                                                                          "
            +  "  	tab5.start_date,                                                                                    "
            +  "  	tab5.start_time,                                                                                    "
            +  "    CONCAT(t.sku_id,'_',t3.warehouse_id, '_', t.order_id) id                                            "
            +  "  FROM                                                                                                  "
            +  "  	b_in_order_goods t                                                                                  "
            +  "  	LEFT JOIN b_in_order t1 ON t.order_id = t1.id                                                       "
            +  "  	LEFT JOIN b_order t2 ON t1.id = t2.serial_id                                                        "
            +  "  	AND t2.serial_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                             "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		tab3.`name` warehouse_name,                                                                     "
            +  "  		tab3.id warehouse_id,                                                                           "
            +  "  		tab.order_id,                                                                                   "
            +  "  		tab2.warehouse_type in_warehouse_type,                                                          "
            +  "  		tab3.warehouse_type out_warehouse_type,                                                         "
            +  "  		tab.out_plan_detail_id                                                                          "
            +  "  	FROM                                                                                                "
            +  "  		b_schedule tab                                                                                  "
            +  "  		INNER JOIN b_monitor tab1 ON tab.id = tab1.schedule_id                                          "
            +  "  		AND tab1.`status` IN ( '4', '5', '6', '7' )                                                     "
            +  "  		LEFT JOIN m_warehouse tab2 ON tab2.id = tab.in_warehouse_id                                     "
            +  "  		LEFT JOIN m_warehouse tab3 ON tab3.id = tab.out_warehouse_id                                    "
            +  "  	WHERE                                                                                               "
            +  "  		tab1.`status` IN ('"+ DictConstant.DICT_B_MONITOR_STATUS_FOUR +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_FIVE +"',"
            +  "        '"+ DictConstant.DICT_B_MONITOR_STATUS_SIX +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_SEVEN +"')"
            +  "  		AND tab2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                                                                   "
            +  "  		AND tab3.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_ZX +"'                                                                   "
            +  "  	GROUP BY                                                                                            "
            +  "  		tab.out_warehouse_id,                                                                           "
            +  "  		tab.order_id                                                                                    "
            +  "  	) t3 ON t3.order_id = t2.id                                                                         "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			SUM( tt2.actual_count ) actual_count,                                                       "
            +  "  			tt2.plan_detail_id                                                                          "
            +  "  		FROM                                                                                            "
            +  "  			b_out tt2                                                                                   "
            +  "  		WHERE                                                                                           "
//            +  "  			tt2.`status` = '"+ DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                "
            +  "  			AND DATE_FORMAT( tt2.e_dt, '%Y%m%d' ) = DATE_FORMAT( NOW(), '%Y%m%d' )                      "
            +  "  		GROUP BY                                                                                        "
            +  "  			tt2.plan_detail_id                                                                          "
            +  "  		) tab4 ON tab4.plan_detail_id = t3.out_plan_detail_id                                          "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			SUM( tt3.actual_count ) actual_count,                                                       "
            +  "  			DATE_FORMAT( tt3.e_dt, '%Y-%m-%d' ) start_date,                                             "
            +  "  			DATE_FORMAT( tt3.e_dt, '%H:%i:%s' ) start_time,                                             "
            +  "  			tt3.plan_detail_id                                                                          "
            +  "  		FROM                                                                                            "
            +  "  			b_out tt3                                                                                   "
            +  "  		WHERE                                                                                           "
//            +  "  			tt3.`status` = '"+ DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                "
            +  "  		GROUP BY                                                                                        "
            +  "  			tt3.plan_detail_id                                                                          "
            +  "  		) tab5 ON tab5.plan_detail_id = t3.out_plan_detail_id                                          "
            +  "  	LEFT JOIN m_goods_spec t4 ON t4.id = t.sku_id                                                       "
            +  "  	LEFT JOIN m_goods t5 ON t4.goods_id = t5.id                                                         "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( tt.actual_count ) actual_count,                                                            "
            +  "  		ifnull( tt2.order_id, tt1.order_id ) order_id,                                                  "
            +  "  		tt.sku_id                                                                                       "
            +  "  	FROM                                                                                                "
            +  "  		b_in tt                                                                                         "
            +  "  		LEFT JOIN b_in_extra tt1 ON tt.id = tt1.in_id                                                   "
            +  "  		AND tt1.order_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                         "
            +  "  		LEFT JOIN b_in_plan_detail tt2 ON tt.plan_detail_id = tt2.id                                    "
            +  "  		AND tt2.order_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                         "
            +  "  	WHERE                                                                                               "
            +  "  		tt.`status` = '"+ DictConstant.DICT_B_IN_STATUS_TWO +"'                                      "
            +  "  		AND tt.type = '"+ DictConstant.DICT_B_IN_TYPE_ZERO +"'                                            "
            +  "  	GROUP BY                                                                                            "
            +  "  		ifnull( tt2.order_id, tt1.order_id ),                                                           "
            +  "  		tt.sku_id                                                                                       "
            +  "  	) t6 ON t6.order_id = t.order_id                                                                    "
            +  "  	AND t6.sku_id = t.sku_id                                                                            "
            +  "  WHERE                                                                                                 "
            +  "  	t1.`status` != '"+ DictConstant.DICT_B_IN_ORDER_STATUS_ONE +"'                                      "
            +  "  	AND t5.CODE = '"+ SystemConstants.PRODUCT_COMM_CODE.COMM_RICE_CODE +"'                              "
            +  "  	AND t3.in_warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                            "
            +  "  	AND t3.out_warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_ZX +"'                           "
            +  "    AND (t1.contract_no LIKE CONCAT('%', #{p1.contract_no}, '%') OR #{p1.contract_no} IS NULL OR #{p1.contract_no} = '')"
            +  "    AND (t3.warehouse_id = #{p1.warehouse_id} OR #{p1.warehouse_id} IS NULL OR #{p1.warehouse_id} = '') "
            +  "    AND (CONCAT(t4.name, '_', t5.name) LIKE CONCAT ('%', #{p1.goods_name}, '%') OR #{p1.goods_name} IS NULL OR #{p1.goods_name} = '')"
            +  "    AND (tab5.start_date >= #{p1.start_date_start} OR #{p1.start_date_start} IS NULL OR #{p1.start_date_start} = '')"
            +  "    AND (tab5.start_date <= #{p1.start_date_end} OR #{p1.start_date_end} IS NULL OR #{p1.start_date_end} = '')"
            +  "    AND (DATE_FORMAT(t1.contract_dt, '%Y-%m-%d') >= #{p1.batch} OR #{p1.batch} IS NULl OR #{p1.batch} = '')"
            +  "  GROUP BY                                                                                              "
            +  "  	t.sku_id,                                                                                           "
            +  "  	t3.warehouse_id,                                                                                    "
            +  "  	t.order_id                                                                                          "
    )
    IPage<PurchaseContractStatisticsVo> selectPageList(@Param("p1") PurchaseContractStatisticsVo param, Page<PurchaseContractStatisticsVo> pageCondition);

    @Select(""
            +  "  SELECT                                                                                                "
            +  "  	SUM( t.num ) contract_num,                                                                          "
            +  "  	SUM(t6.actual_count) in_qty,                                                                        "
            +  "  	SUM(tab4.actual_count) today_out_qty,                                                               "
            +  "  	SUM(tab5.actual_count) out_qty                                                                      "
            +  "  FROM                                                                                                  "
            +  "  	b_in_order_goods t                                                                                  "
            +  "  	LEFT JOIN b_in_order t1 ON t.order_id = t1.id                                                       "
            +  "  	LEFT JOIN b_order t2 ON t1.id = t2.serial_id                                                        "
            +  "  	AND t2.serial_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                             "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		tab3.`name` warehouse_name,                                                                     "
            +  "  		tab3.id warehouse_id,                                                                           "
            +  "  		tab.order_id,                                                                                   "
            +  "  		tab2.warehouse_type in_warehouse_type,                                                          "
            +  "  		tab3.warehouse_type out_warehouse_type,                                                          "
            +  "  		tab.out_plan_detail_id                                                                          "
            +  "  	FROM                                                                                                "
            +  "  		b_schedule tab                                                                                  "
            +  "  		INNER JOIN b_monitor tab1 ON tab.id = tab1.schedule_id                                          "
            +  "  		AND tab1.`status` IN ( '4', '5', '6', '7' )                                                     "
            +  "  		LEFT JOIN m_warehouse tab2 ON tab2.id = tab.in_warehouse_id                                     "
            +  "  		LEFT JOIN m_warehouse tab3 ON tab3.id = tab.out_warehouse_id                                    "
            +  "  	WHERE                                                                                               "
            +  "  		tab1.`status` IN ('"+ DictConstant.DICT_B_MONITOR_STATUS_FOUR +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_FIVE +"',"
            +  "        '"+ DictConstant.DICT_B_MONITOR_STATUS_SIX +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_SEVEN +"')"
            +  "  		AND tab2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                         "
            +  "  		AND tab3.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_ZX +"'                         "
            +  "  	GROUP BY                                                                                            "
            +  "  		tab.out_warehouse_id,                                                                           "
            +  "  		tab.order_id                                                                                    "
            +  "  	) t3 ON t3.order_id = t2.id                                                                         "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			SUM( tt2.actual_count ) actual_count,                                                       "
            +  "  			tt2.plan_detail_id                                                                          "
            +  "  		FROM                                                                                            "
            +  "  			b_out tt2                                                                                   "
            +  "  		WHERE                                                                                           "
//            +  "  			tt2.`status` = '"+ DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                "
            +  "  			AND DATE_FORMAT( tt2.e_dt, '%Y%m%d' ) = DATE_FORMAT( NOW(), '%Y%m%d' )                      "
            +  "  		GROUP BY                                                                                        "
            +  "  			tt2.plan_detail_id                                                                          "
            +  "  		) tab4 ON tab4.plan_detail_id = t3.out_plan_detail_id                                          "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			SUM( tt3.actual_count ) actual_count,                                                       "
            +  "  			DATE_FORMAT( tt3.e_dt, '%Y-%m-%d' ) start_date,                                             "
            +  "  			DATE_FORMAT( tt3.e_dt, '%H:%i:%s' ) start_time,                                             "
            +  "  			tt3.plan_detail_id                                                                          "
            +  "  		FROM                                                                                            "
            +  "  			b_out tt3                                                                                   "
            +  "  		WHERE                                                                                           "
//            +  "  			tt3.`status` = '"+ DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                "
            +  "  		GROUP BY                                                                                        "
            +  "  			tt3.plan_detail_id                                                                          "
            +  "  		) tab5 ON tab5.plan_detail_id = t3.out_plan_detail_id                                           "
            +  "  	LEFT JOIN m_goods_spec t4 ON t4.id = t.sku_id                                                       "
            +  "  	LEFT JOIN m_goods t5 ON t4.goods_id = t5.id                                                         "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( tt.actual_count ) actual_count,                                                            "
            +  "  		ifnull( tt2.order_id, tt1.order_id ) order_id,                                                  "
            +  "  		tt.sku_id                                                                                       "
            +  "  	FROM                                                                                                "
            +  "  		b_in tt                                                                                         "
            +  "  		LEFT JOIN b_in_extra tt1 ON tt.id = tt1.in_id                                                   "
            +  "  		AND tt1.order_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                         "
            +  "  		LEFT JOIN b_in_plan_detail tt2 ON tt.plan_detail_id = tt2.id                                    "
            +  "  		AND tt2.order_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                         "
            +  "  	WHERE                                                                                               "
            +  "  		tt.`status` = '"+ DictConstant.DICT_B_IN_STATUS_TWO +"'                                      "
            +  "  		AND tt.type = '"+ DictConstant.DICT_B_IN_TYPE_ZERO +"'                                            "
            +  "  	GROUP BY                                                                                            "
            +  "  		ifnull( tt2.order_id, tt1.order_id ),                                                           "
            +  "  		tt.sku_id                                                                                       "
            +  "  	) t6 ON t6.order_id = t.order_id                                                                    "
            +  "  	AND t6.sku_id = t.sku_id                                                                            "
            +  "  WHERE                                                                                                 "
            +  "  	t1.`status` != '"+ DictConstant.DICT_B_IN_ORDER_STATUS_ONE +"'                                      "
            +  "  	AND t5.CODE = '"+ SystemConstants.PRODUCT_COMM_CODE.COMM_RICE_CODE +"'                              "
            +  "  	AND t3.in_warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                            "
            +  "  	AND t3.out_warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_ZX +"'                           "
            +  "    AND (t1.contract_no LIKE CONCAT('%', #{p1.contract_no}, '%') OR #{p1.contract_no} IS NULL OR #{p1.contract_no} = '')"
            +  "    AND (t3.warehouse_id = #{p1.warehouse_id} OR #{p1.warehouse_id} IS NULL OR #{p1.warehouse_id} = '') "
            +  "    AND (CONCAT(t4.name, '_', t5.name) LIKE CONCAT ('%', #{p1.goods_name}, '%') OR #{p1.goods_name} IS NULL OR #{p1.goods_name} = '')"
            +  "    AND (tab5.start_date >= #{p1.start_date_start} OR #{p1.start_date_start} IS NULL OR #{p1.start_date_start} = '')"
            +  "    AND (tab5.start_date <= #{p1.start_date_end} OR #{p1.start_date_end} IS NULL OR #{p1.start_date_end} = '')"
            +  "    AND (DATE_FORMAT(t1.contract_dt, '%Y-%m-%d') >= #{p1.batch} OR #{p1.batch} IS NULl OR #{p1.batch} = '')"
    )
    PurchaseContractStatisticsVo getListSum(@Param("p1") PurchaseContractStatisticsVo param);

    @Select(""
            +  "  SELECT                                                                                                "
            +  "  	count(1)                                                                          "
            +  "  FROM                                                                                                  "
            +  "  	b_in_order_goods t                                                                                  "
            +  "  	LEFT JOIN b_in_order t1 ON t.order_id = t1.id                                                       "
            +  "  	LEFT JOIN b_order t2 ON t1.id = t2.serial_id                                                        "
            +  "  	AND t2.serial_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                             "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		tab3.`name` warehouse_name,                                                                     "
            +  "  		tab3.id warehouse_id,                                                                           "
            +  "  		tab.order_id,                                                                                   "
            +  "  		tab2.warehouse_type in_warehouse_type,                                                          "
            +  "  		tab3.warehouse_type out_warehouse_type,                                                         "
            +  "  		tab.out_plan_detail_id                                                                          "
            +  "  	FROM                                                                                                "
            +  "  		b_schedule tab                                                                                  "
            +  "  		INNER JOIN b_monitor tab1 ON tab.id = tab1.schedule_id                                          "
            +  "  		AND tab1.`status` IN ( '4', '5', '6', '7' )                                                     "
            +  "  		LEFT JOIN m_warehouse tab2 ON tab2.id = tab.in_warehouse_id                                     "
            +  "  		LEFT JOIN m_warehouse tab3 ON tab3.id = tab.out_warehouse_id                                    "
            +  "  	WHERE                                                                                               "
            +  "  		tab1.`status` IN ('"+ DictConstant.DICT_B_MONITOR_STATUS_FOUR +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_FIVE +"',"
            +  "        '"+ DictConstant.DICT_B_MONITOR_STATUS_SIX +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_SEVEN +"')"
            +  "  		AND tab2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                         "
            +  "  		AND tab3.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_ZX +"'                         "
            +  "  	GROUP BY                                                                                            "
            +  "  		tab.out_warehouse_id,                                                                           "
            +  "  		tab.order_id                                                                                    "
            +  "  	) t3 ON t3.order_id = t2.id                                                                         "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			SUM( tt3.actual_count ) actual_count,                                                       "
            +  "  			DATE_FORMAT( tt3.e_dt, '%Y-%m-%d' ) start_date,                                             "
            +  "  			DATE_FORMAT( tt3.e_dt, '%H:%i:%s' ) start_time,                                             "
            +  "  			tt3.plan_detail_id                                                                          "
            +  "  		FROM                                                                                            "
            +  "  			b_out tt3                                                                                   "
            +  "  		WHERE                                                                                           "
//            +  "  			tt3.`status` = '"+ DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                "
            +  "  		GROUP BY                                                                                        "
            +  "  			tt3.plan_detail_id                                                                          "
            +  "  		) tab5 ON tab5.plan_detail_id = t3.out_plan_detail_id                                          "
            +  "  	LEFT JOIN m_goods_spec t4 ON t4.id = t.sku_id                                                       "
            +  "  	LEFT JOIN m_goods t5 ON t4.goods_id = t5.id                                                         "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( tt.actual_count ) actual_count,                                                            "
            +  "  		ifnull( tt2.order_id, tt1.order_id ) order_id,                                                  "
            +  "  		tt.sku_id                                                                                       "
            +  "  	FROM                                                                                                "
            +  "  		b_in tt                                                                                         "
            +  "  		LEFT JOIN b_in_extra tt1 ON tt.id = tt1.in_id                                                   "
            +  "  		AND tt1.order_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                         "
            +  "  		LEFT JOIN b_in_plan_detail tt2 ON tt.plan_detail_id = tt2.id                                    "
            +  "  		AND tt2.order_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                         "
            +  "  	WHERE                                                                                               "
            +  "  		tt.`status` = '"+ DictConstant.DICT_B_IN_STATUS_TWO +"'                                      "
            +  "  		AND tt.type = '"+ DictConstant.DICT_B_IN_TYPE_ZERO +"'                                            "
            +  "  	GROUP BY                                                                                            "
            +  "  		ifnull( tt2.order_id, tt1.order_id ),                                                           "
            +  "  		tt.sku_id                                                                                       "
            +  "  	) t6 ON t6.order_id = t.order_id                                                                    "
            +  "  	AND t6.sku_id = t.sku_id                                                                            "
            +  "  WHERE                                                                                                 "
            +  "  	t1.`status` != '"+ DictConstant.DICT_B_IN_ORDER_STATUS_ONE +"'                                      "
            +  "  	AND t5.CODE = '"+ SystemConstants.PRODUCT_COMM_CODE.COMM_RICE_CODE +"'                              "
            +  "  	AND t3.in_warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                            "
            +  "  	AND t3.out_warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_ZX +"'                           "
            +  "    AND (t1.contract_no LIKE CONCAT('%', #{p1.contract_no}, '%') OR #{p1.contract_no} IS NULL OR #{p1.contract_no} = '')"
            +  "    AND (t3.warehouse_id = #{p1.warehouse_id} OR #{p1.warehouse_id} IS NULL OR #{p1.warehouse_id} = '') "
            +  "    AND (CONCAT(t4.name, '_', t5.name) LIKE CONCAT ('%', #{p1.goods_name}, '%') OR #{p1.goods_name} IS NULL OR #{p1.goods_name} = '')"
            +  "    AND (tab5.start_date >= #{p1.start_date_start} OR #{p1.start_date_start} IS NULL OR #{p1.start_date_start} = '')"
            +  "    AND (tab5.start_date <= #{p1.start_date_end} OR #{p1.start_date_end} IS NULL OR #{p1.start_date_end} = '')"
            +  "    AND (DATE_FORMAT(t1.contract_dt, '%Y-%m-%d') >= #{p1.batch} OR #{p1.batch} IS NULl OR #{p1.batch} = '')"
    )
    int selectExportNum(@Param("p1") PurchaseContractStatisticsVo param);

    @Select("<script>"
            +  "  SELECT                                                                                                "
            +  "  	t1.contract_no,                                                                                     "
            +  "  	t3.`warehouse_name`,                                                                                "
            +  "  	t5.`name` goods_name,                                                                               "
            +  "  	t4.`spec` ,                                                                                         "
            +  "  	SUM( t.num ) contract_num,                                                                          "
            +  "  	t6.actual_count in_qty,                                                                             "
            +  "  	tab4.actual_count today_out_qty,                                                                    "
            +  "  	tab5.actual_count out_qty,                                                                          "
            +  "  	tab5.start_date,                                                                                    "
            +  "  	tab5.start_time,                                                                                    "
            +  "    CONCAT(t.sku_id,'_',t3.warehouse_id, '_', t.order_id) id                                            "
            +  "  FROM                                                                                                  "
            +  "  	b_in_order_goods t                                                                                  "
            +  "  	LEFT JOIN b_in_order t1 ON t.order_id = t1.id                                                       "
            +  "  	LEFT JOIN b_order t2 ON t1.id = t2.serial_id                                                        "
            +  "  	AND t2.serial_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                             "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		tab3.`name` warehouse_name,                                                                     "
            +  "  		tab3.id warehouse_id,                                                                           "
            +  "  		tab.order_id,                                                                                   "
            +  "  		tab2.warehouse_type in_warehouse_type,                                                          "
            +  "  		tab3.warehouse_type out_warehouse_type,                                                         "
            +  "  		tab.out_plan_detail_id                                                                          "
            +  "  	FROM                                                                                                "
            +  "  		b_schedule tab                                                                                  "
            +  "  		INNER JOIN b_monitor tab1 ON tab.id = tab1.schedule_id                                          "
            +  "  		AND tab1.`status` IN ( '4', '5', '6', '7' )                                                     "
            +  "  		LEFT JOIN m_warehouse tab2 ON tab2.id = tab.in_warehouse_id                                     "
            +  "  		LEFT JOIN m_warehouse tab3 ON tab3.id = tab.out_warehouse_id                                    "
            +  "  	WHERE                                                                                               "
            +  "  		tab1.`status` IN ('"+ DictConstant.DICT_B_MONITOR_STATUS_FOUR +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_FIVE +"',"
            +  "        '"+ DictConstant.DICT_B_MONITOR_STATUS_SIX +"', '"+ DictConstant.DICT_B_MONITOR_STATUS_SEVEN +"')"
            +  "  		AND tab2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                                                                   "
            +  "  		AND tab3.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_ZX +"'                                                                   "
            +  "  	GROUP BY                                                                                            "
            +  "  		tab.out_warehouse_id,                                                                           "
            +  "  		tab.order_id                                                                                    "
            +  "  	) t3 ON t3.order_id = t2.id                                                                         "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			SUM( tt2.actual_count ) actual_count,                                                       "
            +  "  			tt2.plan_detail_id                                                                          "
            +  "  		FROM                                                                                            "
            +  "  			b_out tt2                                                                                   "
            +  "  		WHERE                                                                                           "
//            +  "  			tt2.`status` = '"+ DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                "
            +  "  			AND DATE_FORMAT( tt2.e_dt, '%Y%m%d' ) = DATE_FORMAT( NOW(), '%Y%m%d' )                      "
            +  "  		GROUP BY                                                                                        "
            +  "  			tt2.plan_detail_id                                                                          "
            +  "  		) tab4 ON tab4.plan_detail_id = t3.out_plan_detail_id                                          "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			SUM( tt3.actual_count ) actual_count,                                                       "
            +  "  			DATE_FORMAT( tt3.e_dt, '%Y-%m-%d' ) start_date,                                             "
            +  "  			DATE_FORMAT( tt3.e_dt, '%H:%i:%s' ) start_time,                                             "
            +  "  			tt3.plan_detail_id                                                                          "
            +  "  		FROM                                                                                            "
            +  "  			b_out tt3                                                                                   "
            +  "  		WHERE                                                                                           "
//            +  "  			tt3.`status` = '"+ DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                "
            +  "  		GROUP BY                                                                                        "
            +  "  			tt3.plan_detail_id                                                                          "
            +  "  		) tab5 ON tab5.plan_detail_id = t3.out_plan_detail_id                                          "
            +  "  	LEFT JOIN m_goods_spec t4 ON t4.id = t.sku_id                                                       "
            +  "  	LEFT JOIN m_goods t5 ON t4.goods_id = t5.id                                                         "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( tt.actual_count ) actual_count,                                                            "
            +  "  		ifnull( tt2.order_id, tt1.order_id ) order_id,                                                  "
            +  "  		tt.sku_id                                                                                       "
            +  "  	FROM                                                                                                "
            +  "  		b_in tt                                                                                         "
            +  "  		LEFT JOIN b_in_extra tt1 ON tt.id = tt1.in_id                                                   "
            +  "  		AND tt1.order_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                         "
            +  "  		LEFT JOIN b_in_plan_detail tt2 ON tt.plan_detail_id = tt2.id                                    "
            +  "  		AND tt2.order_type = '"+ DictConstant.DICT_SYS_CODE_TYPE_B_IN_ORDER +"'                         "
            +  "  	WHERE                                                                                               "
            +  "  		tt.`status` = '"+ DictConstant.DICT_B_IN_STATUS_TWO +"'                                      "
            +  "  		AND tt.type = '"+ DictConstant.DICT_B_IN_TYPE_ZERO +"'                                            "
            +  "  	GROUP BY                                                                                            "
            +  "  		ifnull( tt2.order_id, tt1.order_id ),                                                           "
            +  "  		tt.sku_id                                                                                       "
            +  "  	) t6 ON t6.order_id = t.order_id                                                                    "
            +  "  	AND t6.sku_id = t.sku_id                                                                            "
            +  "  WHERE                                                                                                 "
            +  "  	t1.`status` != '"+ DictConstant.DICT_B_IN_ORDER_STATUS_ONE +"'                                      "
            +  "  	AND t5.CODE = '"+ SystemConstants.PRODUCT_COMM_CODE.COMM_RICE_CODE +"'                              "
            +  "  	AND t3.in_warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                            "
            +  "  	AND t3.out_warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_ZX +"'                           "
            +  "    AND (t1.contract_no LIKE CONCAT('%', #{p1.contract_no}, '%') OR #{p1.contract_no} IS NULL OR #{p1.contract_no} = '')"
            +  "    AND (t3.warehouse_id = #{p1.warehouse_id} OR #{p1.warehouse_id} IS NULL OR #{p1.warehouse_id} = '') "
            +  "    AND (CONCAT(t4.name, '_', t5.name) LIKE CONCAT ('%', #{p1.goods_name}, '%') OR #{p1.goods_name} IS NULL OR #{p1.goods_name} = '')"
            +  "    AND (tab5.start_date &gt;= #{p1.start_date_start} OR #{p1.start_date_start} IS NULL OR #{p1.start_date_start} = '')"
            +  "    AND (tab5.start_date &lt;= #{p1.start_date_end} OR #{p1.start_date_end} IS NULL OR #{p1.start_date_end} = '')"
            +  "    AND (DATE_FORMAT(t1.contract_dt, '%Y-%m-%d') &gt;= #{p1.batch} OR #{p1.batch} IS NULl OR #{p1.batch} = '')"
            +  "    <if test='p1.ids != null and p1.ids.size != 0'>                                                     "
            +  "    AND CONCAT(t.sku_id,'_',t3.warehouse_id, '_', t.order_id) in                                        "
            +  "       <foreach collection='p1.ids' item='item' index='index' open='(' separator=',' close=')'>         "
            +  "        #{item,jdbcType=VARCHAR}                                                                        "
            +  "       </foreach>                                                                                       "
            +  "    </if>                                                                                               "
            +  "  GROUP BY                                                                                              "
            +  "  	t.sku_id,                                                                                           "
            +  "  	t3.warehouse_id,                                                                                    "
            +  "  	t.order_id                                                                                          "
            +  "</script>                                                                                               "
    )
    List<PurchaseContractStatisticsExportVo> getExportList(@Param("p1") PurchaseContractStatisticsVo param);

    /**
     * 查询销售订单汇总
     * @param param
     * @param pageCondition
     * @return
     */
    @Select(""
            +  "  SELECT                                                                                                "
            +  "  	t.contract_no,                                                                                      "
            +  "  	t.id,                                                                                               "
            +  "  	t.order_no,                                                                                         "
            +  "  	IFNULL( tt1.schedule_count, 0 ) schedule_count,                                                     "
            +  "  	tt2.label status_name,                                                                              "
            +  "  	t.contract_dt,                                                                                      "
            +  "  	t.contract_expire_dt,                                                                               "
            +  "  	ifnull( tt3.short_name, tt3.NAME ) client_name,                                                     "
            +  "  	ifnull( tt4.short_name, tt4.NAME ) AS owner_name,                                                   "
            +  "  	tt5.json_objects,                                                                                   "
            +  "  	tt5.out_address                                                                                     "
            +  "  FROM                                                                                                  "
            +  "  	b_out_order t                                                                                       "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		t1.id,                                                                                          "
            +  "  		count( t3.id ) schedule_count                                                                   "
            +  "  	FROM                                                                                                "
            +  "  		b_out_order t1                                                                                  "
            +  "  		INNER JOIN b_order t2 ON t1.id = t2.serial_id                                                   "
            +  "  		AND t2.serial_type = 'b_out_order'                                                              "
            +  "  		INNER JOIN b_schedule t3 ON t2.id = t3.order_id                                                 "
            +  "  	GROUP BY                                                                                            "
            +  "  		t1.id                                                                                           "
            +  "  	) tt1 ON t.id = tt1.id                                                                              "
            +  "  	LEFT JOIN v_dict_info tt2 ON tt2.`code` = 'b_out_order_status'                                      "
            +  "  	AND tt2.dict_value = t.                                                                             "
            +  "  	STATUS LEFT JOIN m_customer tt3 ON t.client_id = tt3.id                                             "
            +  "  	LEFT JOIN m_owner tt4 ON t.owner_id = tt4.id                                                        "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		t4.order_id,                                                                                    "
            +  "  		t7.out_address,                                                                                 "
            +  "  		JSON_ARRAYAGG(                                                                                  "
            +  "  			JSON_OBJECT(                                                                                "
            +  "  				'sku_name',                                                                             "
            +  "  				t5.`name`,                                                                              "
            +  "  				'pm',                                                                                   "
            +  "  				t5.pm,                                                                                  "
            +  "  				'spec',                                                                                 "
            +  "  				t5.spec,                                                                                "
            +  "  				'contract_num',                                                                         "
            +  "  				t4.num,                                                                                 "
            +  "  				'has_handle_count',                                                                     "
            +  "  				IFNULL( t7.has_handle_count, 0 ),                                                       "
            +  "  				'arrived_count',                                                                        "
            +  "  				IFNULL( tab1.arrived_count, 0 ),                                                        "
            +  "  				'in_transit_count',                                                                     "
            +  "  				IFNULL( tab1.in_transit_count, 0 ),                                                     "
            +  "  				'qty_loss',                                                                             "
            +  "  			IFNULL( tab1.qty_loss, 0 ))) json_objects                                                   "
            +  "  	FROM                                                                                                "
            +  "  		b_out_order_goods t4                                                                            "
            +  "  		LEFT JOIN m_goods_spec t5 ON t4.sku_id = t5.id                                                  "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			t6.order_id,                                                                                "
            +  "  			t6.sku_id,                                                                                  "
            +  "  			SUM( t6.has_handle_count ) - SUM( t6.return_qty ) has_handle_count,                         "
            +  "  			GROUP_CONCAT( DISTINCT mt1.short_name ) out_address                                         "
            +  "  		FROM                                                                                            "
            +  "  			b_out_plan_detail t6                                                                        "
            +  "  			LEFT JOIN b_out_order t8 ON t6.order_type = 'b_out_order'                                   "
            +  "  			AND t8.id = t6.order_id                                                                     "
            +  "  			LEFT JOIN m_warehouse mt1 ON t6.warehouse_id = mt1.id                                       "
            +  "  			LEFT JOIN b_out_plan b1 ON t6.plan_id = b1.id                                               "
            +  "  		WHERE                                                                                           "
            +  "  			t6.STATUS IN ( '2', '5', '6', '7' )                                                              "
            +  "  			AND b1.release_order_code IS NOT NULL                                                       "
            +  "  		GROUP BY                                                                                        "
            +  "  			t6.order_id,                                                                                "
            +  "  			t6.sku_id                                                                                   "
            +  "  		) t7 ON t7.order_id = t4.order_id                                                               "
            +  "  		AND t7.sku_id = t4.sku_id                                                                       "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			t15.serial_id,                                                                              "
            +  "  			t10.sku_id,                                                                                 "
            +  "  			SUM(                                                                                        "
            +  "  			IF                                                                                          "
            +  "  			( t9.STATUS = '7', ifnull( t11.qty, t12.qty ), 0 )) arrived_count,                          "
            +  "  			SUM(                                                                                        "
            +  "  			IF                                                                                          "
            +  "  				(                                                                                       "
            +  "  					FIND_IN_SET( t9.STATUS, '4,5,6' ) = 0,                                              "
            +  "  					0,                                                                                  "
            +  "  				ifnull( t13.qty, t14.qty ) - ifnull(t18.qty,0)) ) as in_transit_count,              "
            +  "  			sum(                                                                                        "
            +  "  			CASE                                                                                        "
            +  "  					                                                                                    "
            +  "  					WHEN ifnull( t11.type, t12.type ) = 'b_monitor_unload' THEN                         "
            +  "  					0 ELSE                                                                              "
            +  "  				IF                                                                                      "
            +  "  					(                                                                                   "
            +  "  						ifnull( t13.qty, t14.qty ) <= ifnull( t11.qty, t12.qty ),                       "
            +  "  						0,                                                                              "
            +  "  					ifnull( ifnull( t13.qty, t14.qty ) - ifnull( t11.qty, t12.qty )- ifnull(t18.qty,0), 0 ))               "
            +  "  				END                                                                                     "
            +  "  				) AS qty_loss                                                                           "
            +  "  			FROM                                                                                        "
            +  "  				b_monitor t9                                                                            "
            +  "  				LEFT JOIN b_schedule t10 ON t9.schedule_id = t10.id                                     "
            +  "  				LEFT JOIN b_monitor_in t11 ON t11.monitor_id = t9.id                                    "
            +  "  				LEFT JOIN b_monitor_unload t12 ON t12.monitor_id = t9.id                                "
            +  "  				LEFT JOIN b_monitor_out t13 ON t13.monitor_id = t9.id                                   "
            +  "                LEFT JOIN b_out t16 ON t16.id = t13.out_id                                              "
            +  "                LEFT JOIN b_out_plan t17 ON t17.id = t16.plan_id                                        "
            +  "  				LEFT JOIN b_monitor_delivery t14 ON t14.monitor_id = t9.id                              "
            +  "  				LEFT JOIN b_order t15 ON t10.order_id = t15.id                                          "
            +  "  				AND t15.serial_type = 'b_out_order'                                                     "
            +  "                LEFT JOIN b_return_relation t18 ON t18.serial_id = t9.id AND t18.serial_type = '"+ SystemConstants.SERIAL_TYPE.B_MONITOR+"'                     "
            +  "                AND  t18.STATUS = '"+ DictConstant.DICT_B_RETURN_RELATION_STATUS_TG +"'                                                                        "
            +  "            WHERE t17.release_order_code IS NOT NULL                                                    "
            +  "  			GROUP BY                                                                                    "
            +  "  				t15.serial_id,                                                                          "
            +  "  				t10.sku_id                                                                              "
            +  "  			) tab1 ON tab1.serial_id = t4.order_id                                                      "
            +  "  			AND tab1.sku_id = t4.sku_id                                                                 "
            +  "  		GROUP BY                                                                                        "
            +  "  			t4.order_id                                                                                 "
            +  "  		) tt5 ON tt5.order_id = t.id                                                                    "
            +  "  	WHERE                                                                                               "
            +  "  		t.`status` != '-1'                                                                              "
            +  "    AND (t.contract_no LIKE CONCAT('%', #{p1.contract_no}, '%') OR #{p1.contract_no} IS NULL OR #{p1.contract_no} = '') "
            +  "    AND (t.order_no LIKE CONCAT('%', #{p1.order_no}, '%') OR #{p1.order_no} IS NULL OR #{p1.order_no} = '') "
            +  "    AND (DATE_FORMAT(t.contract_dt, '%Y-%m-%d' ) >= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '') "
    )
    @Results({
            @Result(property = "json_objects", column = "json_objects", javaType = List.class, typeHandler = JsonArrayTypeHandler.class),
    })
    IPage<SalesContractStatisticsVo> selectSalesPageList(@Param("p1") SalesContractStatisticsVo param, Page<SalesContractStatisticsVo> pageCondition);

    @Select(""
            +  "  SELECT                                                                                                "
            +  "  	SUM(tt5.has_handle_count) has_handle_count,                                                         "
            +  "  	SUM(tt5.in_transit_count) in_transit_count,                                                         "
            +  "  	SUM(tt5.arrived_count) arrived_count,                                                               "
            +  "  	SUM(tt5.qty_loss) qty_loss                                                                          "
            +  "  FROM                                                                                                  "
            +  "  	b_out_order t                                                                                       "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		t1.id,                                                                                          "
            +  "  		count( t3.id ) schedule_count                                                                   "
            +  "  	FROM                                                                                                "
            +  "  		b_out_order t1                                                                                  "
            +  "  		INNER JOIN b_order t2 ON t1.id = t2.serial_id                                                   "
            +  "  		AND t2.serial_type = 'b_out_order'                                                              "
            +  "  		INNER JOIN b_schedule t3 ON t2.id = t3.order_id                                                 "
            +  "  	GROUP BY                                                                                            "
            +  "  		t1.id                                                                                           "
            +  "  	) tt1 ON t.id = tt1.id                                                                              "
            +  "  	LEFT JOIN m_customer tt3 ON t.client_id = tt3.id                                                    "
            +  "  	LEFT JOIN m_owner tt4 ON t.owner_id = tt4.id                                                        "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		t4.order_id,                                                                                    "
            +  "  		t7.has_handle_count,                                                                            "
            +  "  		tab1.in_transit_count,                                                                          "
            +  "  		tab1.arrived_count,                                                                             "
            +  "  		tab1.qty_loss                                                                                   "
            +  "  	FROM                                                                                                "
            +  "  		b_out_order_goods t4                                                                            "
            +  "  		LEFT JOIN m_goods_spec t5 ON t4.sku_id = t5.id                                                  "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			t6.order_id,                                                                                "
            +  "  			t6.sku_id,                                                                                  "
            +  "  			SUM( t6.has_handle_count ) -SUM( t6.return_qty ) has_handle_count                           "
            +  "  		FROM                                                                                            "
            +  "  			b_out_plan_detail t6                                                                        "
            +  "  			LEFT JOIN b_out_order t8 ON t6.order_type = 'b_out_order'                                   "
            +  "  			AND t8.id = t6.order_id                                                                     "
            +  "  			LEFT JOIN m_warehouse mt1 ON t6.warehouse_id = mt1.id                                       "
            +  "  			LEFT JOIN b_out_plan b1 ON t6.plan_id = b1.id                                               "
            +  "  		WHERE                                                                                           "
            +  "  			t6.STATUS IN ( '2', '5', '6', '7' )                                                              "
            +  "  			AND b1.release_order_code IS NOT NULL                                                       "
            +  "  		GROUP BY                                                                                        "
            +  "  			t6.order_id,                                                                                "
            +  "  			t6.sku_id                                                                                   "
            +  "  		) t7 ON t7.order_id = t4.order_id                                                               "
            +  "  		AND t7.sku_id = t4.sku_id                                                                       "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			t15.serial_id,                                                                              "
            +  "  			t10.sku_id,                                                                                 "
            +  "  			SUM(                                                                                        "
            +  "  			IF                                                                                          "
            +  "  			( t9.STATUS = '7', ifnull( t11.qty, t12.qty ), 0 )) arrived_count,                          "
            +  "  			SUM(                                                                                        "
            +  "  			IF                                                                                          "
            +  "  				(                                                                                       "
            +  "  					FIND_IN_SET( t9.STATUS, '4,5,6' ) = 0,                                              "
            +  "  					0,                                                                                  "
            +  "  				ifnull( t13.qty, t14.qty )- ifnull(t18.qty,0))) in_transit_count,                                          "
            +  "  			sum(                                                                                        "
            +  "  			CASE                                                                                        "
            +  "  					                                                                                    "
            +  "  					WHEN ifnull( t11.type, t12.type ) = 'b_monitor_unload' THEN                         "
            +  "  					0 ELSE                                                                              "
            +  "  				IF                                                                                      "
            +  "  					(                                                                                   "
            +  "  						ifnull( t13.qty, t14.qty ) <= ifnull( t11.qty, t12.qty ),                       "
            +  "  						0,                                                                              "
            +  "  					ifnull( ifnull( t13.qty, t14.qty ) - ifnull( t11.qty, t12.qty ) - ifnull(t18.qty,0), 0 ))               "
            +  "  				END                                                                                     "
            +  "  				) AS qty_loss                                                                           "
            +  "  			FROM                                                                                        "
            +  "  				b_monitor t9                                                                            "
            +  "  				LEFT JOIN b_schedule t10 ON t9.schedule_id = t10.id                                     "
            +  "  				LEFT JOIN b_monitor_in t11 ON t11.monitor_id = t9.id                                    "
            +  "  				LEFT JOIN b_monitor_unload t12 ON t12.monitor_id = t9.id                                "
            +  "  				LEFT JOIN b_monitor_out t13 ON t13.monitor_id = t9.id                                   "
            +  "                LEFT JOIN b_out t16 ON t16.id = t13.out_id                                              "
            +  "                LEFT JOIN b_out_plan t17 ON t17.id = t16.plan_id                                        "
            +  "  				LEFT JOIN b_monitor_delivery t14 ON t14.monitor_id = t9.id                              "
            +  "  				LEFT JOIN b_order t15 ON t10.order_id = t15.id                                          "
            +  "  				AND t15.serial_type = 'b_out_order'                                                     "
            +  "                LEFT JOIN b_return_relation t18 ON t18.serial_id = t9.id AND t18.serial_type = '"+ SystemConstants.SERIAL_TYPE.B_MONITOR+"'                     "
            +  "                AND  t18.STATUS = '"+ DictConstant.DICT_B_RETURN_RELATION_STATUS_TG +"'                                                                        "
            +  "            WHERE t17.release_order_code IS NOT NULL                                                    "
            +  "  			GROUP BY                                                                                    "
            +  "  				t15.serial_id,                                                                          "
            +  "  				t10.sku_id                                                                              "
            +  "  			) tab1 ON tab1.serial_id = t4.order_id                                                      "
            +  "  			AND tab1.sku_id = t4.sku_id                                                                 "
//            +  "  		GROUP BY                                                                                        "
//            +  "  			t4.order_id                                                                                 "
            +  "  		) tt5 ON tt5.order_id = t.id                                                                    "
            +  "  	WHERE                                                                                               "
            +  "  		t.`status` != '-1'                                                                              "
            +  "    AND (t.contract_no LIKE CONCAT('%', #{p1.contract_no}, '%') OR #{p1.contract_no} IS NULL OR #{p1.contract_no} = '') "
            +  "    AND (t.order_no LIKE CONCAT('%', #{p1.order_no}, '%') OR #{p1.order_no} IS NULL OR #{p1.order_no} = '') "
            +  "    AND (DATE_FORMAT(t.contract_dt, '%Y-%m-%d' ) >= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '') "
    )
    SalesContractStatisticsVo selectSalesPageListSum(@Param("p1") SalesContractStatisticsVo param);

    /**
     * 查询销售订单汇总
     * @param param
     * @return
     */
    @Select("<script>"
            +  "  SELECT                                                                                                "
            +  "  	t.contract_no,                                                                                      "
            +  "  	t.order_no,                                                                                         "
            +  "  	IFNULL( tt1.schedule_count, 0 ) schedule_count,                                                     "
            +  "  	tt2.label status_name,                                                                              "
            +  "  	t.contract_dt,                                                                                      "
            +  "  	t.contract_expire_dt,                                                                               "
            +  "  	ifnull( tt3.short_name, tt3.NAME ) client_name,                                                     "
            +  "  	ifnull( tt4.short_name, tt4.NAME ) AS owner_name,                                                   "
            +  "  	tt5.json_objects,                                                                                   "
            +  "  	concat(round(tt5.execute_processing, 2), '%') execute_processing,                                   "
            +  "    @row_num:= @row_num+ 1 as no,                                                                       "
            +  "  	tt5.out_address                                                                                     "
            +  "  FROM                                                                                                  "
            +  "  	b_out_order t                                                                                       "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		t1.id,                                                                                          "
            +  "  		count( t3.id ) schedule_count                                                                   "
            +  "  	FROM                                                                                                "
            +  "  		b_out_order t1                                                                                  "
            +  "  		INNER JOIN b_order t2 ON t1.id = t2.serial_id                                                   "
            +  "  		AND t2.serial_type = 'b_out_order'                                                              "
            +  "  		INNER JOIN b_schedule t3 ON t2.id = t3.order_id                                                 "
            +  "  	GROUP BY                                                                                            "
            +  "  		t1.id                                                                                           "
            +  "  	) tt1 ON t.id = tt1.id                                                                              "
            +  "  	LEFT JOIN v_dict_info tt2 ON tt2.`code` = 'b_out_order_status'                                      "
            +  "  	AND tt2.dict_value = t.                                                                             "
            +  "  	STATUS LEFT JOIN m_customer tt3 ON t.client_id = tt3.id                                             "
            +  "  	LEFT JOIN m_owner tt4 ON t.owner_id = tt4.id                                                        "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		t4.order_id,                                                                                    "
            +  "  		t7.out_address,                                                                                 "
            +  "  		(IFNULL(SUM(t7.has_handle_count), 0) / sum(t4.num) * 100) execute_processing,              "
            +  "  		JSON_ARRAYAGG(                                                                                  "
            +  "  			JSON_OBJECT(                                                                                "
            +  "  				'sku_name',                                                                             "
            +  "  				t5.`name`,                                                                              "
            +  "  				'pm',                                                                                   "
            +  "  				t5.pm,                                                                                  "
            +  "  				'spec',                                                                                 "
            +  "  				t5.spec,                                                                                "
            +  "  				'contract_num',                                                                         "
            +  "  				t4.num,                                                                                 "
            +  "  				'has_handle_count',                                                                     "
            +  "  				IFNULL( t7.has_handle_count, 0 ),                                                       "
            +  "  				'arrived_count',                                                                        "
            +  "  				IFNULL( tab1.arrived_count, 0 ),                                                        "
            +  "  				'in_transit_count',                                                                     "
            +  "  				IFNULL( tab1.in_transit_count, 0 ),                                                     "
            +  "  				'qty_loss',                                                                             "
            +  "  			IFNULL( tab1.qty_loss, 0 ))) json_objects                                                   "
            +  "  	FROM                                                                                                "
            +  "  		b_out_order_goods t4                                                                            "
            +  "  		LEFT JOIN m_goods_spec t5 ON t4.sku_id = t5.id                                                  "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			t6.order_id,                                                                                "
            +  "  			t6.sku_id,                                                                                  "
            +  "  			SUM( t6.has_handle_count )  - SUM( t6.return_qty ) has_handle_count,                        "
            +  "  			GROUP_CONCAT( DISTINCT mt1.short_name ) out_address                                         "
            +  "  		FROM                                                                                            "
            +  "  			b_out_plan_detail t6                                                                        "
            +  "  			LEFT JOIN b_out_order t8 ON t6.order_type = 'b_out_order'                                   "
            +  "  			AND t8.id = t6.order_id                                                                     "
            +  "  			LEFT JOIN m_warehouse mt1 ON t6.warehouse_id = mt1.id                                       "
            +  "  			LEFT JOIN b_out_plan b1 ON t6.plan_id = b1.id                                               "
            +  "  		WHERE                                                                                           "
            +  "  			t6.STATUS IN ( '2', '5', '6', '7' )                                                              "
            +  "  			AND b1.release_order_code IS NOT NULL                                                       "
            +  "  		GROUP BY                                                                                        "
            +  "  			t6.order_id,                                                                                "
            +  "  			t6.sku_id                                                                                   "
            +  "  		) t7 ON t7.order_id = t4.order_id                                                               "
            +  "  		AND t7.sku_id = t4.sku_id                                                                       "
            +  "  		LEFT JOIN (                                                                                     "
            +  "  		SELECT                                                                                          "
            +  "  			t15.serial_id,                                                                              "
            +  "  			t10.sku_id,                                                                                 "
            +  "  			SUM(                                                                                        "
            +  "  			IF                                                                                          "
            +  "  			( t9.STATUS = '7', ifnull( t11.qty, t12.qty ), 0 )) arrived_count,                          "
            +  "  			SUM(                                                                                        "
            +  "  			IF                                                                                          "
            +  "  				(                                                                                       "
            +  "  					FIND_IN_SET( t9.STATUS, '4,5,6' ) = 0,                                              "
            +  "  					0,                                                                                  "
            +  "  				ifnull( t13.qty, t14.qty )- ifnull(t18.qty,0))) in_transit_count,                  "
            +  "  			sum(                                                                                        "
            +  "  			CASE                                                                                        "
            +  "  					                                                                                    "
            +  "  					WHEN ifnull( t11.type, t12.type ) = 'b_monitor_unload' THEN                         "
            +  "  					0 ELSE                                                                              "
            +  "  				IF                                                                                      "
            +  "  					(                                                                                   "
            +  "  						ifnull( t13.qty, t14.qty ) &lt;= ifnull( t11.qty, t12.qty ),                       "
            +  "  						0,                                                                              "
            +  "  					ifnull( ifnull( t13.qty, t14.qty ) - ifnull( t11.qty, t12.qty ) - ifnull(t18.qty,0), 0 ))               "
            +  "  				END                                                                                     "
            +  "  				) AS qty_loss                                                                           "
            +  "  			FROM                                                                                        "
            +  "  				b_monitor t9                                                                            "
            +  "  				LEFT JOIN b_schedule t10 ON t9.schedule_id = t10.id                                     "
            +  "  				LEFT JOIN b_monitor_in t11 ON t11.monitor_id = t9.id                                    "
            +  "  				LEFT JOIN b_monitor_unload t12 ON t12.monitor_id = t9.id                                "
            +  "  				LEFT JOIN b_monitor_out t13 ON t13.monitor_id = t9.id                                   "
            +  "                LEFT JOIN b_out t16 ON t16.id = t13.out_id                                              "
            +  "                LEFT JOIN b_out_plan t17 ON t17.id = t16.plan_id                                        "
            +  "  				LEFT JOIN b_monitor_delivery t14 ON t14.monitor_id = t9.id                              "
            +  "  				LEFT JOIN b_order t15 ON t10.order_id = t15.id                                          "
            +  "  				AND t15.serial_type = 'b_out_order'                                                     "
            +  "                LEFT JOIN b_return_relation t18 ON t18.serial_id = t9.id AND t18.serial_type = '"+ SystemConstants.SERIAL_TYPE.B_MONITOR+"'                     "
            +  "                AND  t18.STATUS = '"+ DictConstant.DICT_B_RETURN_RELATION_STATUS_TG +"'                                                                        "
            +  "            WHERE t17.release_order_code IS NOT NULL                                                    "
            +  "  			GROUP BY                                                                                    "
            +  "  				t15.serial_id,                                                                          "
            +  "  				t10.sku_id                                                                              "
            +  "  			) tab1 ON tab1.serial_id = t4.order_id                                                      "
            +  "  			AND tab1.sku_id = t4.sku_id                                                                 "
            +  "  		GROUP BY                                                                                        "
            +  "  			t4.order_id                                                                                 "
            +  "  		) tt5 ON tt5.order_id = t.id                                                                    "
            +  "   ,(select @row_num:=0) tt6                                                                            "
            +  "  	WHERE                                                                                               "
            +  "  		t.`status` != '-1'                                                                              "
            +  "    AND (t.contract_no LIKE CONCAT('%', #{p1.contract_no}, '%') OR #{p1.contract_no} IS NULL OR #{p1.contract_no} = '') "
            +  "    AND (t.order_no LIKE CONCAT('%', #{p1.order_no}, '%') OR #{p1.order_no} IS NULL OR #{p1.order_no} = '') "
            +  "    AND (DATE_FORMAT(t.contract_dt, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '') "
            +  "   <if test='p1.ids != null and p1.ids.length!=0'>                                                     "
            +  "    AND t.id in                                                                                         "
            +  "        <foreach collection='p1.ids' item='item' index='index' open='(' separator=',' close=')'>        "
            +  "         #{item}                                                                                        "
            +  "        </foreach>                                                                                      "
            +  "   </if>                                                                                                "
            +  "</script>"
    )
    @Results({
            @Result(property = "json_objects", column = "json_objects", javaType = List.class, typeHandler = JsonArrayTypeHandler.class),
    })
    List<SalesContractStatisticsVo> selectSalesListExport(@Param("p1") SalesContractStatisticsVo param);
}
